package Test_Web;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

public class MovieList extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public MovieList() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// Get request cookies
		Cookie[] cookie = request.getCookies();
        String sqlQuery = "";
        for(Cookie obj : cookie){
        	 
			if(obj.getName().equals("sqlQuery")){
				sqlQuery = obj.getValue();
				break;
			}
		}
        
        // add group by
        sqlQuery += " GROUP BY movies.id, movies.title, movies.year, movies.director";
        
        // Get request parameters
        // Sorting Parameters
        String sorting = request.getParameter("sortingOrder");
        if(sorting != null)
        {
        if(sorting.equals("yeardesc"))
        	sqlQuery += " ORDER BY year DESC";
        else if(sorting.equals("yearasc"))
        	sqlQuery += " ORDER BY year ASC";
        else if(sorting.equals("titledesc"))
        	sqlQuery += " ORDER BY title DESC";
        else
        	sqlQuery += " ORDER BY title ASC";
        }
        
        // Page Parameters
        int intLimit = 1000000; // unlimited
        int intCurrentRecord = 0; // assume it starts at first record
        
        // Page limit parameters
        String limit = request.getParameter("limitPages");
        if(limit != null)
        {
        	 if(limit.equals("10"))
             	intLimit = 10;
             else if(limit.equals("25"))
             	intLimit = 25;
             else if(limit.equals("50"))
             	intLimit = 50;
             else
             	intLimit = 1000000; // unlimited
        }
        
        // Current record parameters
        String currentRecord = request.getParameter("currentRecord");
        if(currentRecord != null)
        {
        	intCurrentRecord = Integer.parseInt(currentRecord);
        }
        
        sqlQuery += " LIMIT "+ intCurrentRecord +","+ intLimit;
        
        // begin to build HTML page
     	response.setContentType("text/html");

        PrintWriter out = response.getWriter();

        out.println("<HTML><HEAD><TITLE>Movie List</TITLE></HEAD>");
        out.println("<BODY><H1>Movie List</H1>");
     		
 		// execute SQL query
 		try
 		{
 			try 
 			{
 				Class.forName("com.mysql.jdbc.Driver").newInstance();
 			} 
 			catch (Exception e) 
 			{
 				e.printStackTrace();
 			}
 			
 			Connection connect;
 			connect = DriverManager.getConnection("jdbc:mysql:///moviedb","testuser","testpass");
 			Statement tempStatement = connect.createStatement();
 			
 			// Where sqlQuery is inserted
 			String command = sqlQuery;
 		
 			// execute query
 			ResultSet resultSet = tempStatement.executeQuery(command);
 			
 			// Print out table
 			out.println("<TABLE border>");
 			// Display table headers
 			
 			while(resultSet.next())
 			{
 				// Display id, title, year and director
 				String id = resultSet.getString("id");
 				String title = resultSet.getString("title");
 				String year = resultSet.getString("year");
 				String director = resultSet.getString("director");
 				
 				out.println("<tr>" +
                         "<td>"+ id +"</td>" + "<td><a href = 'SingleMovie?id="+id+"'>" + title +"</a><td>"+ "<td>"+ year +"</td>" + "<td>"+ director +"</td>");
 				
 				// Display genres
 				String genreQuery = "select genres.name from movies,genres,genres_in_movies where movies.id = movie_id AND genres.id = genre_id AND movies.id = " +
                         id;
 				Statement tempStatement2 = connect.createStatement();
 				ResultSet genreResultSet = tempStatement2.executeQuery(genreQuery);
 				out.println("<td>");
 				while(genreResultSet.next())
 				{
 					String genre = genreResultSet.getString("name");
 					out.println(genre + " | ");
 				}
 				out.println("</td>");
 				
 				// Display star
 				String starQuery = "select stars.first_name, stars.last_name, stars.id from movies,stars,stars_in_movies where movies.id = movie_id AND stars.id = star_id AND movies.id = " +
                         id;
 				Statement tempStatement3 = connect.createStatement();
 				ResultSet starResultSet = tempStatement3.executeQuery(starQuery);
 				out.println("<td>");
 				while(starResultSet.next())
 				{
 					String firstName = starResultSet.getString("first_name");
 					String lastName = starResultSet.getString("last_name");
 					String name = firstName + " " + lastName;
 					String starId = starResultSet.getString("id");
 	 				out.println("<a href = 'SingleStar?id="+starId+"'>" + name +"</a>");
 	 				out.println(" | ");
 					
 				}
 				out.println("</td>");
 				
 				out.println("</tr>");
 			}
 			 out.println("</TABLE>");

 			// Display Sorting Options
 			String appendThis = "";
 			if(request.getParameter("limitPages") != null)
			{
				 appendThis += "&limitPages=" + request.getParameter("limitPages");
			}
 			if(request.getParameter("currentRecord") != null)
 			{
 				appendThis += "&currentRecord=" + request.getParameter("currentRecord");
 			}
 			appendThis+= "'";
 			 out.println("<a href = 'MovieList?sortingOrder=titleasc"+ appendThis +"> Title Ascending </a> | " +
 			 		"<a href = 'MovieList?sortingOrder=titledesc"+ appendThis +"> Title Ascending </a> | " +
 			 		"<a href = 'MovieList?sortingOrder=yearasc"+ appendThis +"> Year Ascending </a> | " +
 			 		"<a href = 'MovieList?sortingOrder=yeardesc"+ appendThis +"> Year Descending </a>");
 			 
 			// Display Limit Options
  			appendThis = "";
  			if(request.getParameter("sortingOrder") != null)
 			{
 				 appendThis += "&sortingOrder=" + request.getParameter("sortingOrder");
 			}
  			if(request.getParameter("currentRecord") != null)
  			{
  				appendThis += "&currentRecord=" + request.getParameter("currentRecord");
  			}
  			appendThis+= "'";
  			out.println("<br>Limit number of pages to: <a href = 'MovieList?limitPages=10"+ appendThis +"> 10 </a> | " +
  			 		"<a href = 'MovieList?sortingOrder=25"+ appendThis +"> 25 </a> | " +
  			 		"<a href = 'MovieList?sortingOrder=50"+ appendThis +"> 50 </a> | " +
  			 		"<a href = 'MovieList?sortingOrder=1000000"+ appendThis +"> Unlimited </a>");
 			
 			// Display Forward/Back
  			int appendRecordBackward = intCurrentRecord;
  			int appendRecordForward = intCurrentRecord;
  			if(request.getParameter("limitPages") != null)
  			{
				appendRecordBackward = intCurrentRecord - Integer.parseInt(request.getParameter("limitPages"));
				if (appendRecordBackward < 0)
				{
					appendRecordBackward = 0;
				}
				appendRecordForward = intCurrentRecord + Integer.parseInt(request.getParameter("limitPages"));
  			}
  			appendThis = "";
  			if(request.getParameter("sortingOrder") != null)
 			{
 				 appendThis += "&sortingOrder=" + request.getParameter("sortingOrder");
 			}
  			if(request.getParameter("limitPages") != null)
			{
				 appendThis += "&limitPages=" + request.getParameter("limitPages");
			}
  			appendThis+= "'";
  			out.println("<br><a href = 'MovieList?currentRecord="+ appendRecordForward + appendThis +"> Forward </a> | " +
  			 		"<a href = 'MovieList?currentRecord="+ appendRecordBackward + appendThis +"> Back </a>");
  			
 			// Shopping Cart and Main Page Link
 			out.println("<br><br><br><br><br>");
 			out.println("<a href = 'ShoppingCart'>Shopping Cart</a> | " + 
 					"<a href = 'MainPage.jsp'>Main Page</a>");
 			
 			 // close connections
             resultSet.close();
             tempStatement.close();
             connect.close();
          	 out.close();
 		}
 		catch (SQLException ex) {
             while (ex != null) {
                   System.out.println ("SQL Exception:  " + ex.getMessage ());
                   ex = ex.getNextException ();
               }  // end while
           }  // end catch SQLException

 		catch(java.lang.Exception ex)
           {
               out.println("<HTML>" +
                           "<HEAD><TITLE>" +
                           "MovieDB: Error" +
                           "</TITLE></HEAD>\n<BODY>" +
                           "<P>SQL error in doGet: " +
                           ex.getMessage() + "</P></BODY></HTML>");
               return;
           }
	}
	
	public void responseRedirect()
	{
		
	}

}
